import re
from collections import defaultdict

from openpyxl.reader.excel import load_workbook

from config import recharge_class

template_name = 'D:\\恒星\\2024年下学期\\程序设计基础\期末考试\\程序设计基础学生成绩录入模板[刘兴]3-4班.xlsx'

read_files = [
    'D:\\恒星\\2024年下学期\\程序设计基础\\期末考试\\2024软件技术专科1班期末成绩汇总.xlsx',
    'D:\\恒星\\2024年下学期\\程序设计基础\\期末考试\\2024软件技术专科2班期末成绩汇总.xlsx',
    'D:\\恒星\\2024年下学期\\程序设计基础\\期末考试\\2024软件技术专科3班期末成绩汇总.xlsx',
    'D:\\恒星\\2024年下学期\\程序设计基础\\期末考试\\2024软件技术专科4班期末成绩汇总.xlsx',
    'D:\\恒星\\2024年下学期\\程序设计基础\\期末考试\\重修期末成绩汇总.xlsx',
]

pingri_chengji = dict()
kaoshi_chengji = dict()

def read_data_pingri_chengji():
    for filename in read_files:
        wb = load_workbook(filename,data_only=True)
        sheet = wb['平时成绩登记表']
        max_row = sheet.max_row
        for i in range(6, max_row+1):
            _id = str(sheet.cell(i,2).value)
            _name =  sheet.cell(i,3).value
            _score =  sheet.cell(i,7).value
            pingri_chengji[_id] = (_name,_id,_score)


def write_data_pingri_chengji(file, data): # data is
    # a list with tuple
    workbook = load_workbook(file)
    sheet = workbook.active
    start_row = 2
    for i in range(sheet.max_row):
        row = start_row + i
        _id = sheet.cell(row, 3).value
        if _id in data:
            sheet.cell(row, 5).value = data[_id][-1]
    workbook.save(filename=file)


def read_data_kaoshi_chengji():
    for filename in read_files:
        wb = load_workbook(filename,data_only=True)
        sheet = wb['成绩登记表']
        max_row = sheet.max_row
        for i in range(8, max_row+1):
            _num = str(sheet.cell(i,1).value)
            m = re.match("\\d+", _num)
            if not m:
                continue
            _id = str(sheet.cell(i,2).value)
            _name =  sheet.cell(i,3).value
            _score =  sheet.cell(i,5).value
            kaoshi_chengji[_id] = (_name,_id,_score)

def write_data_kaoshi_chengji(file ,data): # data is
    # a list with tuple
    workbook = load_workbook(file)
    sheet = workbook.active
    start_row = 2
    for i in range(sheet.max_row):
        row = start_row + i
        _id = sheet.cell(row, 3).value
        if _id in data:
            sheet.cell(row, 6).value = data[_id][-1]
    workbook.save(filename=file)

read_data_pingri_chengji()
write_data_pingri_chengji(template_name, pingri_chengji)

read_data_kaoshi_chengji()
write_data_kaoshi_chengji(template_name, kaoshi_chengji)